﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
namespace Com.CMS263.Util
{
    public class SqlHelper
    {


        //private static readonly string connectionString = ConfigurationManager.AppSettings["conn"];

        private static readonly string connectionString = ConfigurationManager.ConnectionStrings["accStr"].ConnectionString;
        /*
          <appSettings><add key="connectionString" value="data source=.;initial catalog=TimeVane;integrated security=True"/></appSettings>
        */

        #region DBHelper
        #region ExecuteNonQuery: 执行SQL语句，返回影响的记录数
        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteNonQuery(string sql)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    try
                    {
                        con.Open();
                        int rows = cmd.ExecuteNonQuery();
                        return rows;
                    }
                    catch (SqlException e)
                    {
                        con.Close();
                        throw e;
                    }
                }
            }
        }
        #endregion

        #region ExecuteNonQuery: 执行带参数的SQL语句(带参)
        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteNonQuery(string sql, params SqlParameter[] parameters)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, con, null, sql, parameters);
                        int rows = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        return rows;
                    }
                    catch (SqlException e)
                    {
                        throw e;
                    }
                }
            }
        }
        #endregion

        #region ExecuteTransaction: 执行多条SQL语句，实现数据库事务
        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="sqlList">多条SQL语句</param>		
        public static int ExecuteTransaction(List<string> sqlList)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                SqlTransaction tx = con.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    int count = 0;
                    for (int i = 0; i < sqlList.Count; i++)
                    {
                        string sql = sqlList[i];
                        if (sql.Trim().Length > 1)
                        {
                            cmd.CommandText = sql;
                            count += cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                    return count;
                }
                catch
                {
                    tx.Rollback();
                    return 0;
                }
            }
        }
        #endregion

        #region ExecuteReader: 执行查询语句，返回SqlDataReader
        /// <summary>
        /// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <returns>SqlDataReader</returns>
        public static OleDbDataReader ExecuteReader(string sql)
        {
            OleDbConnection con = new OleDbConnection(connectionString);
           
            OleDbCommand cmd = new OleDbCommand(sql, con);
            try
            {
                con.Open();
                OleDbDataReader reader = cmd.ExecuteReader();
                return reader;
            }
            catch (SqlException e)
            {
                throw e;
            }

        }
        #endregion

        #region ExecuteReader: 执行查询语句，返回SqlDataReader(带参)
        /// <summary>
        /// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] parameters)
        {
            SqlConnection con = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand();
            try
            {
                PrepareCommand(cmd, con, null, sql, parameters);
                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return reader;
            }
            catch (SqlException e)
            {
                throw e;
            }
        }
        #endregion

        #region ExecuteQuery: 执行查询语句，返回DataTable
        /// <summary>
        /// 执行查询语句，返回DataTable
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <returns>DataTable</returns>
        public static DataTable ExecuteQuery(string sql)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                DataTable table = new DataTable();
                try
                {
                    con.Open();
                    SqlDataAdapter da = new SqlDataAdapter(sql, con);
                    da.Fill(table);
                }
                catch (SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                return table;
            }
        }
        #endregion

        #region ExecuteQuery: 执行查询语句，返回DataTable(带参)
        /// <summary>
        /// 执行查询语句，返回DataTable
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <returns>DataTable</returns>
        public static DataTable ExecuteQuery(string sql, params SqlParameter[] parameters)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, con, null, sql, parameters);
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    DataTable table = new DataTable();
                    try
                    {
                        da.Fill(table);
                        cmd.Parameters.Clear();
                    }
                    catch (SqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    return table;
                }
            }
        }
        #endregion

        #region PrepareCommand: 处理参数
        private static void PrepareCommand(SqlCommand cmd, SqlConnection con, SqlTransaction tran, string cmdText, SqlParameter[] parameters)
        {
            if (con.State != ConnectionState.Open)
                con.Open();
            cmd.Connection = con;
            cmd.CommandText = cmdText;
            if (tran != null)
                cmd.Transaction = tran;
            cmd.CommandType = CommandType.Text;
            if (parameters != null)
            {
                foreach (SqlParameter parameter in parameters)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }
        #endregion

        #region ExecuteScalar: 执行一条计算查询结果语句，返回查询结果object(带参)
        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="sql">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public static object ExecuteScalar(string sql, params SqlParameter[] parameters)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, con, null, sql, parameters);
                        object obj = cmd.ExecuteScalar();
                        cmd.Parameters.Clear();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (SqlException e)
                    {
                        throw e;
                    }
                }
            }
        }
        #endregion

        #region ExecuteScalar: 执行一条计算查询结果语句，返回查询结果object
        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="sql">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public static object ExecuteScalar(string sql)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    try
                    {
                        con.Open();
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (SqlException e)
                    {
                        con.Close();
                        throw e;
                    }
                }
            }
        }
        #endregion
        #endregion

    }
}
